package com.test.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.SortedSet;
import java.util.TreeSet;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.test.objects.Author;
import com.test.objects.Comment;
import com.test.objects.MyPageComparator;
import com.test.objects.MyPageException;
import com.test.objects.Relation;
import com.test.objects.MyPageComparator.compare_type;

@SuppressWarnings({ "unchecked", "deprecation" })
public class MainDAOImpl extends MyPageDAOBase implements MainDAO {
	String PROC_GET_LIMIT_COMMENT = "sp_get_limit_comment";
	String PROC_SAVE_COMMENT = "sp_save_comment";
	String PROC_GET_LIMIT_RELATION = "sp_get_limit_relation";
	
	public Comment[] getMostRecentComments(int authorId, int offset, int size){
		MapSqlParameterSource in = new MapSqlParameterSource();
		in.addValue("in_author_id", authorId).
			addValue("in_offset", offset * 5).
			addValue("in_size", size);
		
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this.dataSource).
				withProcedureName(PROC_GET_LIMIT_COMMENT).
				returningResultSet("comments", 
					new ParameterizedRowMapper<Comment>() {
					public Comment mapRow(ResultSet rs, int rowNum)
	                						throws SQLException {
						Comment cmt = new Comment();
						cmt.setId(rs.getInt("id"));
						cmt.setParentId(rs.getInt("parentId"));
						Author aut = new Author();
						aut.setId(rs.getInt("authorId"));
						aut.setFullname(rs.getString("a_fname"));
						aut.setImageUrl(rs.getString("a_img"));
						cmt.setAuthor(aut);
						cmt.setContent(rs.getString("content"));
						cmt.setUrl(rs.getString("url"));
						cmt.setContentEx(rs.getString("contentEx"));
						cmt.setDateTime(new Date(rs.getTimestamp("postDate").getTime()));
						return cmt;
					}
				});
		
		List<Comment> rawCmts = (List<Comment>)
						jdbcCall.execute(in).get("comments");
		HashMap<Integer, Comment> comments = new HashMap<Integer, Comment>();
		for (Comment cmt : rawCmts){
			if (cmt.getParentId() == 0){
				comments.put(cmt.getId(), cmt);
			}else{
				int parentId = cmt.getParentId();
				comments.get(parentId).addSub(cmt);
			}
		}
		SortedSet<Comment> sortedCmts = new TreeSet<Comment>(
					new MyPageComparator(compare_type.decrease));
		sortedCmts.addAll(comments.values());
		return sortedCmts.toArray(new Comment[] {});
	}
	
	public int saveComment(Comment c) throws MyPageException {
		SqlParameterSource in = new MapSqlParameterSource().
				addValue("in_parent_id", c.getParentId()).
				addValue("in_aut_id", c.getAuthor().getId()).
				addValue("in_content", c.getContent()).
				addValue("in_url", c.getUrl()).
				addValue("in_content_ex", c.getContentEx()).
				addValue("in_post_date", c.getDateTime());
		Map<String, Object> out = executeSP(PROC_SAVE_COMMENT, in);
		return out.get("out_new_cmt_id") != null ? 
					(Integer)out.get("out_new_cmt_id") : 0;
	}
	
	public Relation[] getRelation(int authorId, int min, int max){
		MapSqlParameterSource in = new MapSqlParameterSource();
		in.addValue("in_author_id", authorId).
			addValue("in_min", min).
			addValue("in_max", max);
		
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this.dataSource).
				withProcedureName(PROC_GET_LIMIT_RELATION).
				returningResultSet("relations", 
					new ParameterizedRowMapper<Relation>() {
					public Relation mapRow(ResultSet rs, int rowNum)
	                						throws SQLException {
						Relation rel = new Relation();
						rel.setId(rs.getInt("id"));
						Author aut = new Author();
						aut.setId(rs.getInt("f_Id"));
						aut.setFullname(rs.getString("f_fname"));
						aut.setImageUrl(rs.getString("f_img"));
						rel.setAuthor(aut);
						rel.setRelationStartDate(new Date(
								rs.getTimestamp("f_startDate").getTime()));
						rel.setType(rs.getInt("f_type"));
						rel.setDesc(rs.getString("f_desc"));
						return rel;
					}
				});
		
		List<Relation> relList = (List<Relation>)
						jdbcCall.execute(in).get("relations");
		return relList.toArray(new Relation[] {});
	}
	
	public Author getAuthor(int authorId){
		String sql = "SELECT * FROM author " + 
					"WHERE Id=" + authorId;
		Author author = null;
		try{
			author = new SimpleJdbcTemplate(this.dataSource).
				queryForObject(sql, new ParameterizedRowMapper<Author>() {
					public Author mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Author author = new Author();
						author.setId(rs.getInt("Id"));
						author.setFirstname(rs.getString("firstname"));
						author.setFullname(rs.getString("fullname"));
						author.setAccount(rs.getString("account"));
						author.setPassword(rs.getString("password"));
						author.setDob(new Date(rs.getTimestamp("dob").getTime()));
						author.setAddress(rs.getString("address"));
						author.setImageUrl(rs.getString("imageUrl"));
						author.setDescription(rs.getString("description"));
						return author;
					}});
		}catch(EmptyResultDataAccessException erdaEx){
			System.err.println("[mypage-main] " + 
				erdaEx.getClass().toString() + ": " + erdaEx.getMessage());
		}
		return author;	
	}
	
	public boolean deleteComment(int cmtId) throws MyPageException {
		String sql = "DELETE FROM comment " +
					"WHERE parentId=" + cmtId + " OR id="+cmtId;
		try {
			int outVal = new SimpleJdbcTemplate(this.dataSource).update(sql);
			return outVal == 1;
		}catch(DataAccessException daEx){
			System.err.println("[mypage-main] " +
				daEx.getClass().toString() + ": " + daEx.getMessage());
			throw new MyPageException(daEx.getMessage());
		}
	}
}
